﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Corsat.Library.DataManager
{
    public partial class Main
    {
        public static List<CourseInReports> Reports_GetAllCourses()
        {
            command.CommandText = @"
SELECT DISTINCT CourseTitleId FROM CoursesMaster ORDER BY CourseTitleId

select CourseId, CourseTitleId, Titles.ItemName CourseTitleName, CourseLevelId, Levels.ItemName CourseLevelName from CoursesMaster
inner join Items Titles on CoursesMaster.CourseTitleId = Titles.ItemId
left join Items Levels on CoursesMaster.CourseLevelId = Levels.ItemId
order by CourseTitleId

";


            command.CommandType = CommandType.Text;

            DataSet dsCourses = new DataSet();

            adapter.Fill(dsCourses);

            List<CourseInReports> Courses = new List<CourseInReports>();

            if (dsCourses.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dsCourses.Tables[0].Rows.Count; i++)
                {
                    int CourseTitleId = Convert.ToInt32(dsCourses.Tables[0].Rows[i]["CourseTitleId"]);

                    DataRow[] drCourses = dsCourses.Tables[1].Select("CourseTitleId = " + CourseTitleId.ToString());

                    if (drCourses.Length > 0)
                    {
                        CourseInReports course = new CourseInReports();
                        if (drCourses.Length == 1)
                        {

                            course.CourseId = Convert.ToInt32(drCourses[0]["CourseId"]);
                            course.CourseName = drCourses[0]["CourseTitleName"].ToString();
                        }
                        else
                        {

                            course.CourseId = Convert.ToInt32(drCourses[0]["CourseId"]);
                            course.CourseName = drCourses[0]["CourseTitleName"].ToString();

                            List<Item> Levels = new List<Item>();

                            for (int o = 0; o < drCourses.Length; o++)
                            {
                                Item tempLevel = new Item();
                                tempLevel.Id = Convert.ToInt32(drCourses[o]["CourseId"]);
                                tempLevel.Value = drCourses[o]["CourseLevelName"].ToString();

                                Levels.Add(tempLevel);
                            }

                            course.Levels = Levels;
                        }

                        Courses.Add(course);
                    }
                }
            }

            return Courses;

        }


        public static CourseInReports GetCourseById(int courseId)
        {
            command.CommandText = @"
select CourseId, CourseTitleId, Titles.ItemName CourseTitleName, CourseLevelId, Levels.ItemName CourseLevelName from CoursesMaster
inner join Items Titles on CoursesMaster.CourseTitleId = Titles.ItemId
left join Items Levels on CoursesMaster.CourseLevelId = Levels.ItemId
where CoursesMaster.CourseId = " + courseId;


            command.CommandType = CommandType.Text;

            DataSet dsCourses = new DataSet();

            adapter.Fill(dsCourses);

            List<CourseInReports> Courses = new List<CourseInReports>();

            if (dsCourses.Tables[0].Rows.Count > 0)
            {
                DataRow drCourse = dsCourses.Tables[0].Rows[0];

                CourseInReports course = new CourseInReports();

                course.CourseId = Convert.ToInt32(drCourse["CourseId"]);
                course.CourseName = drCourse["CourseTitleName"] + " - " + drCourse["CourseLevelName"];
                        //if (drCourse.Length == 1)
                        //{
                        //}
                        //else
                        //{

                        //    course.CourseId = Convert.ToInt32(drCourse["CourseId"]);
                        //    course.CourseName = drCourse["CourseTitleName"].ToString();

                        //    //List<Item> Levels = new List<Item>();

                        //    //for (int o = 0; o < drCourse.Length; o++)
                        //    //{
                        //    //    Item tempLevel = new Item();
                        //    //    tempLevel.Id = Convert.ToInt32(drCourses[o]["CourseId"]);
                        //    //    tempLevel.Value = drCourses[o]["CourseLevelName"].ToString();

                        //    //    Levels.Add(tempLevel);
                        //    //}

                        //    //course.Levels = Levels;
                        //}

                return course;

            }

            return new CourseInReports();

        }


        public static DataTable Reports_GetResults(int CourseId, bool IncludeTelephones)
        {
            DataTable dtResults = new DataTable();

            command.CommandText = @"
SELECT  AccountCourses.AccountSerialInCourse Serial ,
        AccountsDetails.CorsatId SeatNumber ,
        AccountsMaster.ArabicName Name ,
        AccountCourses.Result Degree ,
        Items.ItemName Grade ";
            if (IncludeTelephones)
                command.CommandText += @"
            ,
        STUFF(( SELECT  ' - ' + Number
                FROM    dbo.AccountTelephones t2
                WHERE   accountsmaster.AccountId = t2.AccountId
              FOR
                XML PATH('')
              ), 1, 2, '') [Telephones]";

            command.CommandText += @"
FROM    AccountsDetails
        INNER JOIN AccountCourses ON AccountsDetails.AccountId = AccountCourses.AccountId
                                     AND AccountsDetails.[YEAR] = " +
                                   DateTime.Now.Year + " and AccountCourses.[YEAR] = " + DateTime.Now.Year + @"
         INNER JOIN AccountsMaster ON AccountsMaster.AccountId = AccountsDetails.AccountId
        LEFT OUTER JOIN Items ON AccountCourses.GradeId = Items.ItemId
WHERE   accountsdetails.TechnologycourseResult IS NOT NULL";

            if (IncludeTelephones)
                command.CommandText += @" AND GradeId in (312, 313,314, 315) ";
            else
                command.CommandText += @"
        AND ( CAST(Att_1 AS INT) + CAST(Att_2 AS INT) + CAST(Att_3 AS INT)
              + CAST(Att_4 AS INT) + CAST(Att_5 AS INT) + CAST(Att_6 AS INT)
              + CAST(Att_7 AS INT) ) > 2";

            command.CommandText += @"
        AND CourseId = " + CourseId.ToString() + @"
        AND AccountCourses.Result IS NOT NULL";
            if (IncludeTelephones)
                command.CommandText += @" ORDER BY GradeId desc";
            else
                command.CommandText += @" ORDER BY Serial";

            command.CommandType = CommandType.Text;

            adapter.Fill(dtResults);
            foreach (DataRow dataRow in dtResults.Rows)
            {
                float result;
                float.TryParse(dataRow["Degree"].ToString(), out result);
                dataRow["Degree"] = result.ToString("0.00"); 
            }
            return dtResults;
        }

        public static DataTable Reports_GetAccounts(int CourseId)
        {
            DataSet dsSeatNumbers = new DataSet();

            command.CommandText = @"
select AccountsMaster.AccountId, AccountSerialInCourse Serial, CorsatId SeatNumber, ArabicName Name, 
	CAST(Att_1 as INT) 
+	CAST(Att_2 as INT) 
+	CAST(Att_3 as INT) 
+	CAST(Att_4 as INT) 
+	CAST(Att_5 as INT) 
+	CAST(Att_6 as INT) 
+	CAST(Att_7 as INT) 
as Att_SUM
from AccountsDetails inner join AccountCourses on AccountsDetails.AccountId = AccountCourses.AccountId
and AccountsDetails.[YEAR] = " + DateTime.Now.Year + " and AccountCourses.[YEAR] = " +
                                  DateTime.Now.Year + @"
 inner join AccountsMaster on AccountsMaster.AccountId = AccountsDetails.AccountId
where CourseId = " + CourseId.ToString() + @"
and AccountsDetails.TechnologyCourseResult is not null 
and AccountsDetails.TechnologyCourseResult >= 10
and (CAST(Att_1 as INT) 
+	CAST(Att_2 as INT) 
+	CAST(Att_3 as INT) 
+	CAST(Att_4 as INT) 
+	CAST(Att_5 as INT) 
+	CAST(Att_6 as INT) 
+	CAST(Att_7 as INT)) > 2
order by AccountCourses.AccountSerialInCourse asc

select AccountId, Number from AccountTelephones
";
            command.CommandType = CommandType.Text;

            adapter.Fill(dsSeatNumbers);

            dsSeatNumbers.Tables[0].Columns.Add("Telephones");

            for (int i = 0; i < dsSeatNumbers.Tables[0].Rows.Count; i++)
            {
                DataRow[] Telephones =
                    dsSeatNumbers.Tables[1].Select("AccountId = " +
                                                   dsSeatNumbers.Tables[0].Rows[i]["AccountId"].ToString());
                string strTelephones = "";

                for (int k = 0; k < Telephones.Length; k++)
                {
                    strTelephones += Telephones[k]["Number"].ToString();

                    if (k < Telephones.Length - 1)
                        strTelephones += "\n";
                }

                dsSeatNumbers.Tables[0].Rows[i]["Telephones"] = strTelephones;
            }

            return dsSeatNumbers.Tables[0];
        }

        public static DataTable Reports_GetArchiveData()
        {

            var query = @"
select AccountId, CorsatId, TechnologyCourseId, Year from AccountsDetails where Year = " +
                        DateTime.Now.Year + @" and technologyCourseId is not null order by CorsatId asc


select AccountId, ArabicName from AccountsMaster where AccountId in (
select AccountId from AccountsDetails where Year =  " + DateTime.Now.Year +
                        @"  and technologyCourseId is not null 
)

select * from AccountTelephones where AccountId in 
(
select AccountId from AccountsDetails where Year =  " + DateTime.Now.Year +
                        @"  and technologyCourseId is not null 
)

select * from AccountCourses where Year =  " + DateTime.Now.Year +
                        @"  and GradeId in (312,313,314,315) and Result is not null 
order by Result asc

select CoursesMaster.CourseId, CoursesMaster.CourseTitleId, CourseTitles.ItemName CourseTitleName, CoursesMaster.CourseLevelId , CourseLevels.ItemName CourseLevelName from  CoursesMaster inner join Items CourseTitles on CoursesMaster.CourseTitleId = CourseTitles.ItemId
left join Items CourseLevels on CoursesMaster.CourseLevelId = CourseLevels.ItemId
";
            DataSet dsAccounts = new DataSet();

            command.CommandText = query;
            command.CommandType = CommandType.Text;

            adapter.Fill(dsAccounts);

            dsAccounts.Tables[0].TableName = "AccountsDetails";
            dsAccounts.Tables[1].TableName = "AccountsMaster";
            dsAccounts.Tables[2].TableName = "AccountTelephones";
            dsAccounts.Tables[3].TableName = "AccountCourses";
            dsAccounts.Tables[4].TableName = "CoursesMaster";

            DataTable dtFinal = new DataTable();
            dtFinal.Columns.Add("CorsatId");
            dtFinal.Columns.Add("Name");
            dtFinal.Columns.Add("Telephones");
            dtFinal.Columns.Add("Courses");
            dtFinal.Columns.Add("Technology");

            for (int i = 0; i < dsAccounts.Tables["AccountsDetails"].Rows.Count; i++)
            {
                DataRow drFinal = dtFinal.NewRow();

                int AccountId = Convert.ToInt32(dsAccounts.Tables["AccountsDetails"].Rows[i]["AccountId"]);
                // Get the CorsatId.
                drFinal["CorsatId"] = dsAccounts.Tables["AccountsDetails"].Rows[i]["CorsatId"].ToString();

                // Get the Account's Name
                DataRow[] drAccountName =
                    dsAccounts.Tables["AccountsMaster"].Select("AccountId = " + AccountId.ToString());

                if (drAccountName.Length > 0)
                    drFinal["Name"] = drAccountName[0]["ArabicName"];

                // Get the Telephones
                DataRow[] drTelephones =
                    dsAccounts.Tables["AccountTelephones"].Select("AccountId = " + AccountId.ToString());

                string AccountTelephones = "";

                for (int j = 0; j < drTelephones.Length; j++)
                {
                    AccountTelephones += drTelephones[j]["Number"].ToString();

                    if (j < drTelephones.Length - 1)
                        AccountTelephones += "\n";
                }
                drFinal["Telephones"] = AccountTelephones;

                // Get the courses
                DataRow[] drCourses = dsAccounts.Tables["AccountCourses"].Select("AccountId = " + AccountId.ToString());

                string strAccountCourses = "";

                for (int j = 0; j < drCourses.Length; j++)
                {
                    int CourseId = Convert.ToInt32(drCourses[j]["CourseId"]);

                    string Course = "";

                    DataRow[] drCourseName =
                        dsAccounts.Tables["CoursesMaster"].Select("CourseId = " + CourseId.ToString());

                    if (drCourseName.Length > 0)
                    {
                        Course += drCourseName[0]["CourseTitleName"].ToString();

                        Course += drCourseName[0]["CourseLevelName"] == DBNull.Value
                                      ? ""
                                      : " - " + drCourseName[0]["CourseLevelName"].ToString();
                        double result = (double)drCourses[j]["Result"];
                        Course += " (" + result.ToString("00.00") + ")";

                        strAccountCourses += Course;

                        if (j < drCourses.Length - 1)
                            strAccountCourses += "\n";
                    }
                }

                drFinal["Courses"] = strAccountCourses;

                // Get the Technology Course
                string tech = "";
                if (dsAccounts.Tables["AccountsDetails"].Rows[i]["TechnologyCourseId"] != DBNull.Value)
                {
                    switch (Convert.ToInt32(dsAccounts.Tables["AccountsDetails"].Rows[i]["TechnologyCourseId"]))
                    {
                        case 323:
                            tech = "O";
                            break;

                        case 324:
                            tech = "B";
                            break;

                        case 325:
                            tech = "C";
                            break;

                        case 326:
                            tech = "P";
                            break;

                        case 327:
                            tech = "A";
                            break;

                        case 328:
                            tech = "D";
                            break;
                    }

                    drFinal["Technology"] = tech;

                }



                if (!String.IsNullOrEmpty(drFinal["Courses"].ToString()) &&
                    !String.IsNullOrEmpty(drFinal["Technology"].ToString()))

                    dtFinal.Rows.Add(drFinal);
            }
            return dtFinal;
        }

        public static DataTable Report_GetCertificates()
        {
            var query = @"
select AccountId, CorsatId, TechnologyCourseId, Year from AccountsDetails where Year =  " +
                        DateTime.Now.Year + @"  and technologyCourseId is not null order by CorsatId asc


select AccountId, ArabicName from AccountsMaster where AccountId in (
select AccountId from AccountsDetails where Year =  " + DateTime.Now.Year +
                        @"  and technologyCourseId is not null 
)

select * from AccountTelephones where AccountId in 
(
select AccountId from AccountsDetails where Year =  " + DateTime.Now.Year +
                        @"  and technologyCourseId is not null 
)

select * from AccountCourses where Year =  " + DateTime.Now.Year +
                        @"  and GradeId in (312,313,314,315) and Result is not null
order by Result asc

select CoursesMaster.CourseId, CoursesMaster.CourseTitleId, CourseTitles.ItemName CourseTitleName, CoursesMaster.CourseLevelId , CourseLevels.ItemName CourseLevelName from  CoursesMaster inner join Items CourseTitles on CoursesMaster.CourseTitleId = CourseTitles.ItemId
left join Items CourseLevels on CoursesMaster.CourseLevelId = CourseLevels.ItemId

select * from Items where categoryid = 8
";
            DataSet dsAccounts = new DataSet();

            command.CommandText = query;
            command.CommandType = CommandType.Text;

            adapter.Fill(dsAccounts);

            dsAccounts.Tables[0].TableName = "AccountsDetails";
            dsAccounts.Tables[1].TableName = "AccountsMaster";
            dsAccounts.Tables[2].TableName = "AccountTelephones";
            dsAccounts.Tables[3].TableName = "AccountCourses";
            dsAccounts.Tables[4].TableName = "CoursesMaster";
            dsAccounts.Tables[5].TableName = "TechnologyCourses";

            DataTable dtFinal = new DataTable();
            dtFinal.Columns.Add("CorsatId");
            dtFinal.Columns.Add("Name");
            dtFinal.Columns.Add("Telephones");
            dtFinal.Columns.Add("Courses");
            dtFinal.Columns.Add("Technology");
            dtFinal.Columns.Add("Serial");
            int serial = 0;
            for (int i = 0; i < dsAccounts.Tables["AccountsDetails"].Rows.Count; i++)
            {
                DataRow drFinal = dtFinal.NewRow();

                int AccountId = Convert.ToInt32(dsAccounts.Tables["AccountsDetails"].Rows[i]["AccountId"]);
                // Get the CorsatId.
                drFinal["CorsatId"] = dsAccounts.Tables["AccountsDetails"].Rows[i]["CorsatId"].ToString();

                // Get the Account's Name
                DataRow[] drAccountName =
                    dsAccounts.Tables["AccountsMaster"].Select("AccountId = " + AccountId.ToString());

                if (drAccountName.Length > 0)
                    drFinal["Name"] = drAccountName[0]["ArabicName"];

                // Get the courses
                DataRow[] drCourses = dsAccounts.Tables["AccountCourses"].Select("AccountId = " + AccountId.ToString());

                string strAccountCourses = "";

                for (int j = 0; j < drCourses.Length; j++)
                {
                    int CourseId = Convert.ToInt32(drCourses[j]["CourseId"]);

                    string Course = "";

                    DataRow[] drCourseName =
                        dsAccounts.Tables["CoursesMaster"].Select("CourseId = " + CourseId.ToString());

                    if (drCourseName.Length > 0)
                    {
                        Course += drCourseName[0]["CourseTitleName"].ToString();

                        Course += drCourseName[0]["CourseLevelName"] == DBNull.Value
                                      ? ""
                                      : " - " + drCourseName[0]["CourseLevelName"].ToString();

                        strAccountCourses += Course;

                        if (j < drCourses.Length - 1)
                            strAccountCourses += " / ";
                    }
                }

                drFinal["Courses"] = strAccountCourses;

                // Get the Technology Course
                string tech = "";
                if (dsAccounts.Tables["AccountsDetails"].Rows[i]["TechnologyCourseId"] != DBNull.Value)
                {
                    DataRow[] drTech =
                        dsAccounts.Tables["TechnologyCourses"].Select("ItemId = " +
                                                                      dsAccounts.Tables["AccountsDetails"].Rows[i][
                                                                          "TechnologyCourseId"].ToString());

                    if (drTech.Length > 0)
                        tech = drTech[0]["ItemName"].ToString();
                    //switch (Convert.ToInt32(dsAccounts.Tables["AccountsDetails"].Rows[i]["TechnologyCourseId"]))
                    //{
                    //    case 323:
                    //        tech = "O";
                    //        break;

                    //    case 324:
                    //        tech = "B";
                    //        break;

                    //    case 325:
                    //        tech = "C";
                    //        break;

                    //    case 326:
                    //        tech = "P";
                    //        break;

                    //    case 327:
                    //        tech = "A";
                    //        break;

                    //    case 328:
                    //        tech = "D";
                    //        break;
                    //}

                    drFinal["Technology"] = tech;

                }



                if (!String.IsNullOrEmpty(drFinal["Courses"].ToString()) &&
                    !String.IsNullOrEmpty(drFinal["Technology"].ToString()))
                {
                    drFinal["Serial"] = serial;
                    serial++;
                    dtFinal.Rows.Add(drFinal);
                }
            }
            return dtFinal;
        }



        public static DataTable GetConferenceData(int status, int year, DateTime? filterDate)
        {
            #region Select command Text

            command.CommandText = StoredProcedure.sp_GetConferenceData.ToString();
            command.CommandType = CommandType.StoredProcedure;

            #endregion

            #region Parameters

            command.Parameters.Clear();
            if (status > 0)
            {
                command.Parameters.Add("@ConferenceStatusId", SqlDbType.Int).Value = status;
            }
            if (year > 0)
            {
                command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
            }
            if (filterDate != null)
            {
                command.Parameters.Add("@FilterDate", SqlDbType.DateTime).Value = filterDate.Value.Date;
            }

            #endregion

            #region Get Data

            try
            {
                DataTable dtConferenceData = new DataTable();
                adapter.SelectCommand = command;
                adapter.Fill(dtConferenceData);

                if (dtConferenceData != null && dtConferenceData.Rows.Count > 0)
                    return dtConferenceData;
                else
                    return null;
            }
            catch (Exception exc)
            {
                return null;
            }

            #endregion
        }

    }
}
